package com.wy.util;

import com.wy.campus.Course;
import com.wy.campus.Room;

import com.sun.javafx.binding.StringFormatter;
import org.apache.commons.lang.StringUtils;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 用于将课表信息转换成空闲教室占用情况
 * Created by csd on 2017/11/9.
 */
public class SpareRoomImportFromCourse {


    //用于查询课程的sql
//    private static String courseSql = "SELECT(CASE WHEN func_split (jxlm, ';', 1) = '' THEN func_split (jxlm, ';', 2) WHEN func_split (jxlm, ';', 1) != '' THEN func_split (jxlm, ';', 1) END) AS jxlm FROM	t_coursebksxkjs"
//            + " WHERE jxlm != ';' AND jxlm != ';;;' AND jxlm != ';;;;' AND jxlm != '' AND zxjxjhh='2017-2018-1-1' GROUP BY jxlm";

    private static String courseSql = "select a.jxlm,a.jash,a.skzc,a.skxq,a.skjc,a.cxjc,a.jxxq,a.jxlh from t_coursebksxkjs a " +
            "where a.zxjxjhh='2017-2018-1-2' and jxlm is not null limit ?,?";
    //用于插入空闲教室的sql
    private static String spareRoom_insert = "INSERT INTO `mobilecampus`.`t_room` ( `roomname`,`xn`, `xq`,`schoolid`,`usedate`,`divisionname`,`divisionid`,`class`)"
            + " VALUES" + "(" + "'{roomname}'," +  "'{xn}'," + "'{xq}'," +"'193', " + "'{usedate}'," + ");";

    //校历
    private static String xl = "SELECT * FROM t_schooltime WHERE xn='2017-2018' and xq=1 and schoolid=193";


    // 这一个表
    String js1 = "SELECT jsbh,jxlmc,xqdm,jsmc from t_jsjbxx";

    private static String initWeek = "000000000000000000000000";

    public static void main(String args[]) {
        SpareRoomImportFromCourse spareRoomImportFromCourse = new SpareRoomImportFromCourse();
        spareRoomImportFromCourse.testOracle();
    }

//    public static void go() {
//        try {
//            JSch jsch = new JSch();
//            Session session = jsch.getSession("root", "218.195.56.181", 2242);
//            session.setPassword("Xxzyb%ydxy2016~");
//            session.setConfig("StrictHostKeyChecking", "no");
//            session.connect();
//            System.out.println(session.getServerVersion());//这里打印SSH服务器版本信息
//
//            //ssh -L 192.168.0.102:5555:192.168.0.101:3306 yunshouhu@192.168.0.102  正向代理
//            int assinged_port = session.setPortForwardingL(3308, "218.195.56.181", 3306);//端口映射 转发
////
////            System.out.println("localhost:" + assinged_port);
//
//            //ssh -R 192.168.0.102:5555:192.168.0.101:3306 yunshouhu@192.168.0.102
//            //session.setPortForwardingR("192.168.0.102",5555, "192.168.0.101", 3306);
//            // System.out.println("localhost:  -> ");
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

    /**
     * 一个非常标准的连接mysql数据库的示例代码
     */
    public void testOracle() {
        Connection con = null;// 创建一个数据库连接
        PreparedStatement pre = null;// 创建预编译语句对象，一般都是用这个而不用Statement
        ResultSet result = null;// 创建一个结果集对象
        PreparedStatement pst = null;
        FileWriter fw = null;
        Map<String,Room> rooms = new HashMap<>();
        try {
            File file=new File("苏州外包.sql");
            //System.out.println(file.getAbsolutePath());
            fw = new FileWriter(file);

            System.out.println("开始尝试连接数据库！");
            con = getConnectionMysql();
            System.out.println("连接成功！");
            String sql = courseSql;// 预编译语句，“？”代表参数

            Map<String, List<Date>> map = getSchoolTime(xl, con, pst);
            Map<String, Map<String, String>>  mapseat = new HashMap<>();
//            seat(result, mapseat);
            addRooms(map, sql, con, pst, rooms, mapseat);

            StringBuilder temp = new StringBuilder();
            for (Room room : rooms.values()) {
                List<String> newStringList=new ArrayList<>();
                if(null!=room.getRoomName()){
                    newStringList.add(room.getRoomName());
                }
                if(null!=room.getRoomtype()){
                    newStringList.add(room.getRoomName());
                }
                String roomSql = roomInsertSqlProudce(room);
                temp.append(roomSql);
            }
            fw.append(temp);
            System.out.println("sql长度："+temp.length());
            // 当结果集不为空时
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
                // 注意关闭的顺序，最后使用的最先关闭
                if (result != null)
                    result.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
                System.out.println("数据库连接已关闭！");
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                fw.flush();
                fw.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }

    private String roomInsertSqlProudce(Room room) {

        StringBuilder insertSql = new StringBuilder();
        insertSql.append("INSERT INTO `mobilecampus`.`t_room` ( `roomname`,`xn`, `xq`,`schoolid`,`usedate`,`divisionname`,`divisionid`,`buildingname`");

        for(int i=1;i<13;i++) {
            insertSql.append(",class"+i);
        }
        //TODO 注意每次这里修改学校ID
        insertSql.append(String.format(") VALUES('%s','%s',%d,193,'%s','%s','%s','%s'",room.getRoomName(),room.getXn(),room.getXq(),
                room.getUseDate(),room.getDivisionName(),room.getDivisionId(),room.getBuildingName()==null ? "" : room.getBuildingName()
                ));

        for(int i=0;i<12;i++) {
            insertSql.append(String.format(",'%s'",room.getClazz()[i]==null?"":room.getClazz()[i]));
        }
        insertSql.append(");\n");
        return insertSql.toString();
    }

    private void addRooms(Map<String, List<Date>> map, String sql, Connection conn, PreparedStatement pst,
                          Map<String,Room> rooms,  Map<String, Map<String, String>> mapseat) {
        int j=0 ;
        String countNumSql = "select count(*) as num from t_coursebksxkjs where zxjxjhh='2017-2018-1-1'";

        try {
            pst = conn.prepareStatement(countNumSql);
            ResultSet rs = pst.executeQuery();
            rs.next();
            int totalPage = rs.getInt("num")/5000+1;
            rs.close();//关闭查询结果和statement，以免因为复用造成内存泄露
            pst.close();
            for(int page=0;page<totalPage;page++) {
                List<Course> courses = new ArrayList<>();
                pst = conn.prepareStatement(sql);
                pst.setInt(1,page*5000);
                pst.setInt(2,5000);
                rs = pst.executeQuery();
                while(rs.next()) {
                    Course course = new Course();
                    course.setBuildingName(rs.getString("jxlh"));
                    course.setClassromm(rs.getString("jxlm"));
                    course.setSkzc(rs.getString("skzc"));
                    course.setFirstClass(rs.getInt("skjc"));
                    course.setLastClass(rs.getInt("cxjc")+course.getFirstClass()-1);
                    course.setWeekDay(rs.getInt("skxq"));
                    course.setJxxq(rs.getString("jxxq"));
                    courses.add(course);
                }

               // int currentWeek = 0;
                for (String zc : map.keySet()) {

                    zc = zc.replace("zc_", "");  //这里又将getTime的key替换,原本的为 "zc_1"表示第1周,转变后变为1
                    // System.out.println(zc + "," + map.get(zc));
                    int currentWeek = Integer.valueOf(zc);
                    // Date date = dates.get(i - 1);
                    List<Date> dates = map.get("zc_" + zc);//获取某一周的七天日期
                    for (int i = 1; i <= 7; i++) {//一周所有时间
                        // 一星期
                        //pst.setInt(1, i);
                        //pst.setInt(2, Integer.parseInt(zc));
                        Date date = dates.get(i - 1);
                        // System.out.println(dateUtil.format(date) + "-" + "星期[" +
                        // i + "]");
                        //for (Map<String, String> mapse : mapseat) {//教室

                        for(Course course:courses) {
                            //currenWeek减1是因为数组下标从0开始
                            if(course.getSkzc().charAt(currentWeek-1) == '1'&&course.getWeekDay()==i) {
                                String strFormat = "yyyy-MM-dd";
                                String useDate = null;
                                SimpleDateFormat dateFormat = new SimpleDateFormat(strFormat);
                                if (date != null) {
                                    useDate = dateFormat.format(date);
                                }
                                Room room;
                                if(rooms.containsKey(useDate+course.getClassromm())) {
                                    room = rooms.get(useDate+course.getClassromm());
                                } else {
                                    room = new Room();
                                    rooms.put(useDate+course.getClassromm(),room);
                                    room.setUseDate(useDate);
                                    room.setClazz(new String[12]);
                                    room.setSchoolid(138);
                                    room.setRoomName(course.getClassromm());
                                    room.setDivisionId(course.getJxxq() == null ? "5" : course.getJxxq());
//                                    if (course.getJxxq().equals("02")){
//                                        room.setDivisionName("明秀校区");
//                                    }else if (course.getJxxq().equals("01")){
//                                        room.setDivisionName("长岗校区");
//                                    }else{
//                                        room.setDivisionName("五合校区");
//                                    }
                                    room.setDivisionName("校本部");
                                    room.setXn("2017-2018");
                                    room.setXq(1);
                                    String buildName = course.getBuildingName();
//                                    /**苏州外包*/
//                                    String roomName = course.getClassromm();
//                                    if (!isContainChinese(roomName)){
//                                        buildName = roomName.substring(0,1);
//                                    }else{
//                                        buildName = roomName;
//                                    }
//                                    /**end*/
                                    room.setBuildingName(buildName);
                                }
                                for(int clazz=course.getFirstClass();clazz<=course.getLastClass();clazz++) {
                                    room.getClazz()[clazz-1] = "占用";
                                }
                            }
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    private void seat(ResultSet rs1, Map<String, Map<String, String>> mapseat) throws SQLException {
        while (rs1.next()) {
            // xqm,jxlm,jsmc,jslxdm,skzws,kszws
            // 学区代码，教学楼明，教室名称，教室类型代码，上课座位，考试座位
            String xqm = rs1.getString("xqdm");
            String jxlm = rs1.getString("jxlmc");
            String jsmc = rs1.getString("jsmc");

            // String jslxdm = rs1.getString("jslxdm");

            //int skzws = rs1.getInt("skzws");
            //int kszws = rs1.getInt("kszws");

            String key = jsmc;
            // System.out.println(key);
            Map<String, String> maps = new HashMap<String, String>();
            maps.put("xqm", xqm);
            maps.put("jxlm", jxlm);
            maps.put("jsmc", jsmc);
            mapseat.put(key, maps);
        }
    }

    public Map<String, List<Date>> getSchoolTime(String sql, Connection conn, PreparedStatement pst) {
        //List<SchoolTime> list = schoolTimeRedisDAO.getSchoolTime("2016-2017", 2, 151);
        Map<String, List<Date>> map = new HashMap<>();
        try{
            pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();


            while (rs.next()) {
                int type = rs.getInt("type");
                int xlzc = rs.getInt("zc");
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String starttime = rs.getString("starttime");
                Date startDate=formatter.parse(starttime);
                if (type == 1) {
                    String zc = "zc_" +xlzc;
                    List<Date> date = new ArrayList<>();

                    Calendar calendar = Calendar.getInstance();
                    // System.out.println(zc);
                    for (int i = 0; i < 7; i++) {
                        calendar.setTime(startDate);
                        calendar.add(Calendar.DAY_OF_MONTH, i);
                        date.add(calendar.getTime());
                        // System.out.println(calendar.getTime());
                    }
                    map.put(zc, date);
                }

            }

        }catch(Exception e){
            System.out.println("date is error");
        }
        return map;
    }


    public  Connection getConnectionMysql(){
        Connection conn = null;
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/mobilecampus";
        String user = "root";
        String pwd = "123";
        try {
            Class.forName(driver);
            try {
                conn = DriverManager.getConnection(url, user, pwd);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //判断字符串中是否包含汉字
    public static boolean isContainChinese(String str) {

        Pattern p = Pattern.compile("[\u4e00-\u9fa5]");
        Matcher m = p.matcher(str);
        if (m.find()) {
            return true;
        }
        return false;
    }

/** 未使用

    private int getCxjc(String str) {
        Pattern pattern = Pattern.compile("第(.*?)节");
        Matcher mathcer = pattern.matcher(str);
        String s = "";
        while (mathcer.find()) {
            s = mathcer.group(1);
        }
        if (s.contains(",")) {
            return s.split(",").length;
        }
        return 1;
    }

    private String jashDelDul(String str) {

        StringBuilder sb = new StringBuilder();

        String[] strArr = str.split(";");
        for (int i = 0; i < strArr.length; i++) {
            if (i != 0) {
                if (strArr[i] != strArr[i - 1]) {
                    sb.append(";").append(strArr[i]);
                }
            } else {
                sb.append(strArr[i]);
            }
        }
        return sb.toString();
    }

    private int getSkjc(String str) {
        Pattern pattern = Pattern.compile("第(.*?)节");
        Matcher mathcer = pattern.matcher(str);
        String s = "";
        while (mathcer.find()) {
            s = mathcer.group(1);
        }
        if (s.contains(",")) {
            String[] sArr = s.split(",");
            return Integer.parseInt(sArr[0]);
        }
        return Integer.parseInt(s);
    }

    private String getSkzc(StringBuilder sb, int start, int end) {
        for (; start < end; start++) {
            sb.replace(start - 1, start, "1");
        }
        // System.out.println(sb);

        return sb.toString();
    }
    private String getHour(String string) {
        String i="1";
        try{
            String[] arr=string.split("-");
            i=String.valueOf(Integer.valueOf(arr[0].split(":")[0])-8+1);
        }catch (Exception e) {

        }
        return i;
    }

    private String getSection(String string) {
        if (StringUtils.isEmpty(string)) {
            return "0";
        } else {
            try {
                String[] arr = string.split("-");
                string = String.valueOf(Integer.valueOf(arr[1].split(":")[0])
                        - Integer.valueOf(arr[0].split(":")[0]) + 1);
            } catch (Exception e) {
                string = "0";
            }
            return string;
        }
    }

    */

}
